﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class frmDTChuyenlop : Form
    {
        bool xoa;
        bool temp;       //tùy chọn insert or update
        int count;      //Số lượng sinh viên
        int index;      //ví trí của sinh viên đó trong dữ liệu
        SqlConnection connection = new SqlConnection("Data Source = .; Initial Catalog = SM; Integrated Security = True");
        public frmDTChuyenlop()
        {
            InitializeComponent();
            disable_textbox();
            groupControl3.Visible = false;
            string get_malop = "select * from T03_Lop_hoc";
            DataSet ds1 = FillDataset(get_malop);
            lku_Malopmoi.Properties.DataSource = ds1.Tables[0];
            lku_Malopmoi.Properties.DisplayMember = "MalopID";
            lku_Malopmoi.Properties.ValueMember = "MalopID";
        }

        DataSet FillDataset(string sql)
        {
            DataSet ds = new DataSet();
            try
            {
                SqlDataAdapter adap = new SqlDataAdapter(sql, connection);
                adap.Fill(ds);
                adap.Dispose();
            }
            catch (Exception ex)
            {
                MessageBox.Show("Error " + ex.Message);
            }
            return ds;
        }

        public void get_lay_Masv()
        {
            DataSet ds = FillDataset("Select [MasvID] from T21_Chuyen_lop");
            lay_colMasv.FieldName = "MasvID";
            gridControl2.DataSource = ds.Tables[0];
        }

        public void get_Chuyenlop(DataRow row)
        {
            txtMasv.Text = row["MasvID"].ToString();
            txtHolot.Text = row["Holot"].ToString();
            txtTen.Text = row["Ten"].ToString();
            txtSoQDCL.Text = row["SoQuyDinhChuyenLop"].ToString();
            DateTime NgayCL = DateTime.Parse(row["NgayChuyen"].ToString());
            dNgayCL.EditValue = NgayCL.ToShortDateString();
            txtMalop.Text = row["Malop"].ToString();
            mLydoCL.Text = row["GhiChu"].ToString();
            lku_Malopmoi.Text = row["MaLopChuyenDen"].ToString();
            lku_Malopmoi.ClosePopup();
        }

        private void layoutView1_FocusedColumnChanged(object sender, DevExpress.XtraGrid.Views.Base.FocusedColumnChangedEventArgs e)
        {
            int[] i = layoutView1.GetSelectedRows();
            int j = i[0];
            DataSet ds = FillDataset("select MasvID from T21_Chuyen_lop");
            string masv = ds.Tables[0].Rows[j].ItemArray[0].ToString();
            DataSet ds1 = FillDataset("Select * from T21_Chuyen_lop where MasvID = N'" + masv + "'");
            DataRow row = ds1.Tables[0].Rows[0];
            index = j;
            get_Chuyenlop(row);
        }

        private void frmDTChuyenlop_Load(object sender, EventArgs e)
        {
            get_lay_Masv();
            DataSet ds = FillDataset("select * from T21_Chuyen_lop");
            DataRow row = ds.Tables[0].Rows[0];
            count = ds.Tables[0].Rows.Count;
            get_Chuyenlop(row);
        }

        public void disable_textbox()
        {
            txtMasv.Enabled = false;
            txtHolot.Enabled = false;
            txtTen.Enabled = false;
            txtSoQDCL.Enabled = false;
            dNgayCL.Enabled = false;
            lku_Malopmoi.Enabled = false;
            mLydoCL.Enabled = false;
            txtMalop.Enabled = false;
        }

        public void enable_textbox()
        {
            txtMasv.Enabled = true;
            txtHolot.Enabled = true;
            txtTen.Enabled = true;
            txtSoQDCL.Enabled = true;
            dNgayCL.Enabled = true;
            lku_Malopmoi.Enabled = true;
            mLydoCL.Enabled = true;
            txtMalop.Enabled = true;
        }

        private void btnVedau_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            DataSet ds = FillDataset("Select * from T21_Chuyen_lop");
            DataRow row = ds.Tables[0].Rows[0];
            get_Chuyenlop(row);
            index = ds.Tables[0].Rows.IndexOf(row);
        }

        private void btnLui_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            index--;
            if (index >= 0)
            {
                DataSet ds = FillDataset("Select * from T21_Chuyen_lop");
                DataRow row = ds.Tables[0].Rows[index];
                get_Chuyenlop(row);
                index = ds.Tables[0].Rows.IndexOf(row);
            }
            else
            {
                index++;
                if (xoa == false)
                    MessageBox.Show("Bạn đang ở mẫu tin đầu tiên");
                else
                    btnVedau_Click(sender, e);
            }
        }

        private void btnToi_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            index++;
            if (index < count)
            {
                DataSet ds = FillDataset("Select * from T21_Chuyen_lop");
                DataRow row = ds.Tables[0].Rows[index];
                get_Chuyenlop(row);
                index = ds.Tables[0].Rows.IndexOf(row);

            }
            else
            {
                index--;
                MessageBox.Show("Bạn đang ở mẫu tin cuối cùng");
            }
        }

        private void btnVecuoi_Click(object sender, EventArgs e)
        {
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            DataSet ds = FillDataset("Select * from T21_Chuyen_lop");
            DataRow row = ds.Tables[0].Rows[count - 1];
            get_Chuyenlop(row);
            index = ds.Tables[0].Rows.IndexOf(row);
        }

        private void btnThem_Click(object sender, EventArgs e)
        {
            btnVedau.Enabled = false;
            btnVecuoi.Enabled = false;
            btnLui.Enabled = false;
            btnToi.Enabled = false;
            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnThoat.Enabled = false;
            btnLuu.Enabled = true;
            btnBoqua.Enabled = true;
            btnChonsv.Enabled = true;
            lku_Malopmoi.EditValue = "";
            txtMasv.Text = "";
            txtHolot.Text = "";
            txtTen.Text = "";
            txtSoQDCL.Text = "";
            dNgayCL.EditValue = "";
            mLydoCL.Text = "";
            txtMalop.Text = "";
            txtSoQDCL.Enabled = true;
            dNgayCL.Enabled = true;
            mLydoCL.Enabled = true;
            lku_Malopmoi.Enabled = true;
        }

        private void btnSua_Click(object sender, EventArgs e)
        {
            temp = true;
            btnVedau.Enabled = false;
            btnVecuoi.Enabled = false;
            btnLui.Enabled = false;
            btnToi.Enabled = false;
            btnThem.Enabled = false;
            btnSua.Enabled = false;
            btnXoa.Enabled = false;
            btnThoat.Enabled = false;
            btnLuu.Enabled = true;
            btnBoqua.Enabled = true;
            txtSoQDCL.Enabled = true;
            dNgayCL.Enabled = true;
            mLydoCL.Enabled = true;
            lku_Malopmoi.Enabled = true;
        }

        public void query(string command)
        {

            connection.Open();
            SqlCommand sqlCommand = connection.CreateCommand();
            sqlCommand.CommandText = command;
            SqlDataReader dr = sqlCommand.ExecuteReader();
            connection.Close();
        }

        private void btnXoa_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("Bạn có chắc muốn xóa không?.", "Quản lý sinh viên", MessageBoxButtons.OKCancel, MessageBoxIcon.None) == DialogResult.OK)
            {
                try
                {
                    xoa = true;
                    string str = "delete from T21_Chuyen_lop where MasvID=N'" + txtMasv.Text + "' ";
                    query(str);
                    get_lay_Masv();
                    btnLui_Click(sender, e);
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error: " + err.Message);
                }
            }
        }

        public bool getRowsTable(string str)
        {
            connection.Open();
            SqlCommand sqlCommand = connection.CreateCommand();
            sqlCommand.CommandText = str;
            SqlDataReader dr = sqlCommand.ExecuteReader();
            if (dr.HasRows == true)
            {
                connection.Close();
                return true;
            }
            connection.Close();
            return false;
        }

        private void btnLuu_Click(object sender, EventArgs e)
        {
            bool ktra = dxValidationProvider1.Validate();
            if (ktra)
            {

                if (temp == false)
                {
                    bool check_masv = getRowsTable("select MasvID from T21_Chuyen_lop where MasvID = N'" + txtMasv.Text + "'");
                    if (check_masv == false)
                    {
                        string str = "INSERT INTO T21_Chuyen_lop (MasvID, Holot, Ten, SoQuyDinhChuyenLop, NgayChuyen, MaLopChuyenDen, MaLop, GhiChu) VALUES (N'" + txtMasv.Text + "', N'" + txtHolot.Text + "', N'" + txtTen.Text + "', N'" + txtSoQDCL.Text + "', '" + dNgayCL.Text + "',N'" + lku_Malopmoi.Text + "', N'" + txtMalop.Text + "',  N'" + mLydoCL.Text + "')";
                        query(str);
                    }
                    else
                        MessageBox.Show("Sinh viên này đã tồn tại", "Thông báo");
                }
                else
                {
                    string str = "update T21_Chuyen_lop set MasvID = N'" + txtMasv.Text + "', Holot = N'" + txtHolot.Text + "', Ten = N'" + txtTen.Text + "', SoQuyDinhChuyenLop = N'" + txtSoQDCL.Text + "',  NgayChuyen = '" + dNgayCL.Text + "',MaLopChuyenDen = N'" + lku_Malopmoi.Text + "',MaLop=N'" + txtMalop.Text + "',  GhiChu = N'" + mLydoCL.Text + "' where masv = N'" + txtMasv.Text + "'";
                    query(str);
                }
                disable_textbox();
                lku_Malopmoi.Enabled = false;
                btnVedau.Enabled = true;
                btnVecuoi.Enabled = true;
                btnLui.Enabled = true;
                btnToi.Enabled = true;
                btnThem.Enabled = true;
                btnSua.Enabled = true;
                btnXoa.Enabled = true;
                btnThoat.Enabled = true;
                btnLuu.Enabled = false;
                btnBoqua.Enabled = false;
                get_lay_Masv();
                groupControl3.Visible = false;
            }
            else
                MessageBox.Show("Bạn chưa nhập dữ liệu cần thiết", "Quản lý sinh viên", MessageBoxButtons.OK, MessageBoxIcon.None);
       
        }

        private void btnBoqua_Click(object sender, EventArgs e)
        {
            btnVedau.Enabled = true;
            btnVecuoi.Enabled = true;
            btnLui.Enabled = true;
            btnToi.Enabled = true;
            btnThem.Enabled = true;
            btnSua.Enabled = true;
            btnXoa.Enabled = true;
            btnThoat.Enabled = true;
            btnLuu.Enabled = false;
            btnBoqua.Enabled = false;
            btnChonsv.Enabled = false;
            DataSet ds = FillDataset("Select * from T21_Chuyen_lop");
            DataRow row = ds.Tables[0].Rows[index];
            get_Chuyenlop(row);
            disable_textbox();
            groupControl3.Visible = false;
        }

        private void btnThoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        public void get_gridviewsinhvien(string malop)
        {
            DataSet ds = FillDataset("select [MasvID], [Holot], [Ten] from [T01_Sinh_vien] sv, [T03_Lop_hoc] lh where sv.[MalopID] = lh.[MalopID] and sv.MalopID = N'" + malop + "'");
            gCol_MaSV.FieldName = "MasvID";
            gCol_Holot.FieldName = "Holot";
            gCol_Ten.FieldName = "Ten";
            gridControl1.DataSource = ds.Tables[0];
        }


        private void lkuChoplop_EditValueChanged(object sender, EventArgs e)
        {
            string malop = lkuChoplop.EditValue.ToString();
            get_gridviewsinhvien(malop);
        }

        private void gridView1_RowClick(object sender, DevExpress.XtraGrid.Views.Grid.RowClickEventArgs e)
        {
            string masv = gridView1.GetRowCellValue(e.RowHandle, gCol_MaSV).ToString();
            DataSet ds = FillDataset("select [MasvID], [Holot], [Ten], [MalopID] from [T01_Sinh_vien] where MasvID = N'" + masv + "'");
            DataRow row = ds.Tables[0].Rows[0];
            txtMasv.Text = row["MasvID"].ToString();
            txtHolot.Text = row["Holot"].ToString();
            txtTen.Text = row["Ten"].ToString();
            txtMalop.Text = row["MalopID"].ToString();
        }

        private void btnChonsv_Click(object sender, EventArgs e)
        {
            groupControl3.Visible = true;
            DataSet ds = FillDataset("Select [MalopID], [Tenlop] from [T03_Lop_hoc]");
            lkuChoplop.Properties.DataSource = ds.Tables[0];
        }
    }
}
